﻿using System.Collections.Generic;
using System.Data;

namespace WorkOrder.Business
{
    public class AvgTimeLength
    {
        private DbHelper dbhelper = new DbHelper();

        /**
         * 根据数据库中table_product的记录
         * 1	语音专线
         * 2	数据专线
         * 3	企业信息机
         * 4	电路租用
         * 5	GPRS专线
         * 6	短信直连
         */

        /**
         * 根据数据库中Table_Order_type的记录
         * 1	开通
         * 2	取消
         * 3	调整
         */

        /**
         * 接入模式
         * GPON接入
         * 传统方式接入
         */

        #region 平均历时

        /// <summary>
        /// 
        /// </summary>
        /// <param name="product"></param>
        /// <param name="order_type"></param>
        /// <param name="access_mode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="tmp">查询说明</param>
        /// <returns></returns>
        public DataTable getDatas(string product, string order_type, string access_mode, string startTime,
                                  string endTime, int tmp)
        {
            if (tmp != 0 && tmp != 1 && tmp != 2) return null;
            string sqlstr = "select A.process,A.target_sector," +
                            "dbo.f_WorkDay(A.request_time,A.response_time) as timeLength " +
                            "into #T " +
                            "from Table_Order_record A " +
                            "inner join Table_Work_order D on A.order_id=D.order_id " +
                            "where D.product_id=" + product + " " +
                            "and D.order_type_id=" + order_type + " " +
                            "and D.access_mode='" + access_mode + "' ";
            if (tmp == 0 || tmp == 1)
                sqlstr += "and D.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (D.order_status='已关闭' or D.order_status='待业务确认') ";
            if (tmp == 1 || tmp == 2)
                sqlstr +=
                    "and A.response_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'; ";
            sqlstr +=
                "select process as 工单流程,target_sector as 处理部门,cast(avg(timeLength) as decimal(10,2)) as 平均时长,count(timeLength) as 子单数量 from #T " +
                "group by process,target_sector; " +
                "drop table #T; ";
            return dbhelper.Query("", sqlstr);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="product"></param>
        /// <param name="order_type"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="tmp">查询说明</param>
        /// <returns></returns>
        public DataTable getDatas(string product, string order_type, string startTime, string endTime, int tmp)
        {
            if (tmp != 0 && tmp != 1 && tmp != 2) return null;
            string sqlstr = "select A.process,A.target_sector," +
                            "dbo.f_WorkDay(A.request_time,A.response_time) as timeLength " +
                            "into #T " +
                            "from Table_Order_record A " +
                            "inner join Table_Work_order D on A.order_id=D.order_id " +
                            "where D.product_id=" + product + " " +
                            "and D.order_type_id=" + order_type + " ";
            if (tmp == 0 || tmp == 1)
                sqlstr += "and D.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (D.order_status='已关闭' or D.order_status='待业务确认') ";
            if (tmp == 1 || tmp == 2)
                sqlstr +=
                    "and A.response_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'; ";
            sqlstr +=
                "select process as 工单流程,target_sector as 处理部门,cast(avg(timeLength) as decimal(10,2)) as 平均时长,count(timeLength) as 子单数量 from #T " +
                "group by process,target_sector; " +
                "drop table #T; ";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 求平均时长的工单列表

        public DataTable getAvgTimeOrderList(string product, string order_type, string access_mode, string process,
                                             string dept, string startTime, string endTime, string desc)
        {
            string sqlstr = null;
            if (string.IsNullOrEmpty(access_mode))
            {
                sqlstr =
                    "select distinct B.order_id,B.order_number as 工单号,B.title as 工单主题 from table_order_record as A,table_work_order as B " +
                    "where A.order_id=B.order_id and " +
                    "B.order_type_id=" + order_type + " and " +
                    "B.product_id=" + product + " and ";
                if (desc == "0" || desc == "1")
                    sqlstr += "B.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (B.order_status='已关闭' or B.order_status='待业务确认') and ";
                if (desc == "1" || desc == "2")
                    sqlstr += "A.response_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and ";
                sqlstr += "A.target_sector='" + dept + "' and A.process='" + process + "'";
            }
            else
            {
                sqlstr =
                    "select distinct B.order_id,B.order_number as 工单号,B.title as 工单主题 from table_order_record as A,table_work_order as B " +
                    "where A.order_id=B.order_id and " +
                    "B.order_type_id=" + order_type + " and " +
                    "B.product_id=" + product + " and " +
                    "B.access_mode='" + access_mode + "' and ";
                if (desc == "0" || desc == "1")
                    sqlstr += "B.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (B.order_status='已关闭' or B.order_status='待业务确认') and ";
                if (desc == "1" || desc == "2")
                    sqlstr += "A.response_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and ";
                sqlstr += "A.target_sector='" + dept + "' and A.process='" + process + "'";
            }
            return dbhelper.Query("", sqlstr);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="step">工单环节1:需求分析;2:审核方案;3:数据配置;4:业务测试</param>
        /// <param name="product"></param>
        /// <param name="orderType"></param>
        /// <param name="accessMode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="desc">说明</param>
        /// <returns></returns>
        public DataTable getAvgTimeOrderListofStep(int step, string product, string orderType, string accessMode,
                                                   string startTime, string endTime, string desc)
        {
            string sqlstr = "select distinct order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                            "where " +
                            "product_id=" + product + " " +
                            "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            switch (step)
            {
                case 1:
                    sqlstr += "and task_time is not null " +
                              "and analysis_time is not null ";
                    if (desc == "0" || desc == "1")
                        sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
                    if (desc == "1" || desc == "2")
                        sqlstr += "and task_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                                  "and analysis_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' ";
                    break;
                case 2:
                    sqlstr += "and analysis_time is not null " +
                              "and confirm_time is not null ";
                    if (desc == "0" || desc == "1")
                        sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
                    if (desc == "1" || desc == "2")
                        sqlstr += "and analysis_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                                  "and confirm_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' ";
                    break;
                case 3:
                    sqlstr += "and confirm_time is not null " +
                              "and audit_time is not null ";
                    if (desc == "0" || desc == "1")
                        sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
                    if (desc == "1" || desc == "2")
                        sqlstr += "and confirm_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                                  "and audit_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' ";
                    break;
                case 4:
                    sqlstr += "and audit_time is not null " +
                              "and config_time is not null ";
                    if (desc == "0" || desc == "1")
                        sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
                    if (desc == "1" || desc == "2")
                        sqlstr += "and audit_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                                  "and config_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' ";
                    break;
                case 5:
                    sqlstr += "and config_time is not null " +
                              "and finish_time is not null ";
                    if (desc == "0" || desc == "1" || desc == "2")
                        sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
                    if (desc == "1")
                        sqlstr += "and config_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' ";
                    break;
            }

            sqlstr += "order by order_number;";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="product"></param>
        /// <param name="orderType"></param>
        /// <param name="accessMode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="desc">说明</param>
        /// <returns></returns>
        public DataTable queryAvgTimeLengthByStep(string product, string orderType, string accessMode, string startTime,
                                                  string endTime, int desc)
        {
            if (desc != 0 && desc != 1 && desc != 2) return null;
            List<string> lst = new List<string>();
            string sqlstr = "select dbo.f_WorkDay(task_time,analysis_time) as timeLength " +
                            "into #T from table_work_order " +
                            "where " +
                            "product_id=" + product + " " +
                            "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            sqlstr += "and task_time is not null and analysis_time is not null ";
            if (desc == 0 || desc == 1)
                sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
            if (desc == 1 || desc == 2)
                sqlstr += "and task_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                          "and analysis_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'";
            sqlstr +=
                ";      select 1 as 序号,'需求分析和资源预占' as 工单环节,cast(avg(timeLength) as decimal(10,2)) as 平均时长,count(timeLength) as 数量 into #R from #T;" +
                "drop table #T;";
            lst.Add(sqlstr);

            sqlstr = "select dbo.f_WorkDay(analysis_time,confirm_time) as timeLength " +
                     "into #T from table_work_order " +
                     "where " +
                     "product_id=" + product + " " +
                     "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            sqlstr += "and analysis_time is not null and confirm_time is not null ";
            if (desc == 0 || desc == 1)
                sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
            if (desc == 1 || desc == 2)
                sqlstr += "and analysis_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                          "and confirm_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'";
            sqlstr +=
                ";      insert into #R select 2,'CRM资源预占确认',cast(avg(timeLength) as decimal(10,2)),count(timeLength) from #T;" +
                "drop table #T;";
            lst.Add(sqlstr);

            sqlstr = "select dbo.f_WorkDay(confirm_time,audit_time) as timeLength " +
                     "into #T from table_work_order " +
                     "where " +
                     "product_id=" + product + " " +
                     "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            sqlstr += "and confirm_time is not null and audit_time is not null ";
            if (desc == 0 || desc == 1)
                sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
            if (desc == 1 || desc == 2)
                sqlstr += "and confirm_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                          "and audit_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'";
            sqlstr +=
                ";      insert into #R select 3,'审核方案',cast(avg(timeLength) as decimal(10,2)),count(timeLength) from #T;" +
                "drop table #T;";
            lst.Add(sqlstr);

            sqlstr = "select dbo.f_WorkDay(audit_time,config_time) as timeLength " +
                     "into #T from table_work_order " +
                     "where " +
                     "product_id=" + product + " " +
                     "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            sqlstr += "and audit_time is not null and config_time is not null ";
            if (desc == 0 || desc == 1)
                sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
            if (desc == 1 || desc == 2)
                sqlstr += "and audit_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                          "and config_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'";
            sqlstr +=
                ";      insert into #R select 4,'数据配置',cast(avg(timeLength) as decimal(10,2)),count(timeLength) from #T;" +
                "drop table #T;";
            lst.Add(sqlstr);

            sqlstr = "select dbo.f_WorkDay(config_time,finish_time) as timeLength " +
                     "into #T from table_work_order " +
                     "where " +
                     "product_id=" + product + " " +
                     "and order_type_id=" + orderType + " ";
            if (!string.IsNullOrEmpty(accessMode))
                sqlstr += "and access_mode='" + accessMode + "' ";
            sqlstr += "and config_time is not null and finish_time is not null ";
            if (desc == 0 || desc == 1 || desc == 2)
                sqlstr += "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') ";
            if (desc == 1)
                sqlstr += "and config_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59'";
            sqlstr +=
                ";      insert into #R select 5,'业务测试',cast(avg(timeLength) as decimal(10,2)),count(timeLength) from #T;" +
                "drop table #T;";
            sqlstr += "select 序号,工单环节,平均时长,数量 from #R order by 序号;" +
                      "drop table #R;";
            lst.Add(sqlstr);

            return dbhelper.ExecuteTransRetrunTable("", lst);
        }
    }
}
